﻿-- Script n°005: "photo" table.

BEGIN;

-- Sequence on photo id
DROP SEQUENCE IF EXISTS seq_photo;

CREATE SEQUENCE seq_photo
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;

ALTER TABLE seq_photo OWNER TO chelioudbdev;
GRANT ALL ON TABLE seq_photo TO chelioudbdev;
GRANT SELECT, UPDATE ON TABLE seq_photo TO cheliouwebdev;

-- Photo table
DROP TABLE IF EXISTS photo;

CREATE TABLE photo (
   id                   NUMERIC              		NOT NULL	DEFAULT nextval('seq_photo'::regclass),
   code                 VARCHAR(23)          		NOT NULL,
   shootingdate         timestamp with time zone	NOT NULL,
   direction            VARCHAR(9)           		NOT NULL,
   titlekey             VARCHAR(255)         		NOT NULL,
   latitude             NUMERIC(9,7)         		NOT NULL,
   longitude            NUMERIC(10,7)        		NOT NULL,
   additiondate         timestamp with time zone	NOT NULL,
   camera               VARCHAR(255)         		NOT NULL,
   focal                NUMERIC(3,1)          		NOT NULL,
   iso                  NUMERIC(5)           		NOT NULL,
   focaldistance        NUMERIC              		NOT NULL,
   flashmode            VARCHAR(100)         		NOT NULL,
   focaldistance35mm    NUMERIC              		NOT NULL,
   whitebalance         VARCHAR(6)           		NOT NULL,
   relativeaperture     NUMERIC(4,1)          		NOT NULL,
   exposuretime         NUMERIC(4)          		NOT NULL,
   layers               BOOLEAN             		NULL,
   blackandwhite        BOOLEAN              		NULL,
   brightness           BOOLEAN              		NULL,
   contrast             BOOLEAN              		NULL,
   straighteningup      BOOLEAN              		NULL,
   model                BOOLEAN              		NOT NULL,
   modelname            VARCHAR(52)          		NULL,
   country              VARCHAR(100)         		NOT NULL,
   city                 VARCHAR(100)         		NOT NULL,
   department           VARCHAR(100)         		NULL,
   CONSTRAINT pk_photo PRIMARY KEY(id),
   CONSTRAINT ak_unique_photo_code_photo UNIQUE(code)
)WITH (
  OIDS=FALSE
);

ALTER TABLE photo OWNER TO chelioudbdev;
GRANT ALL ON TABLE photo TO chelioudbdev;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE photo TO cheliouwebdev;

COMMIT;
